by Mick Davison (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 8th February 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Get SQL Server Agent Status from client M\C without having SQL Server Client objects installed locally, just using a stored procedure.
API Declarations
Private Rs As New ADODB.Recordset
VBcoders
CREATE PROCEDURE [dbo].[spAgentStatus]
@user varchar(30), -- VALID SQL Server Login
@pwd varchar(30) -- VALID Login Password
AS
-- Returned information
DECLARE @source varchar (255)
DECLARE @description varchar (255)
DECLARE @status_msg varchar(100)
DECLARE @Status int
-- Internal variables
DECLARE @object int
DECLARE @hr int
-- Create SQLDMO Object
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT
SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
RETURN
END
-- Call Method to connect to the server
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @@ServerName, @user, @pwd
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT
SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
RETURN
END
-- Get property value for the SQL Server Agent current status
DECLARE @property varchar(255)
EXEC @hr = sp_OAGetProperty @object, 'JobServer.Status', @property OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT
SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
RETURN
END
--Get status information to return
SET @status_msg = CASE @property WHEN 1 THEN 'SQL Server Agent is running'
WHEN 3 THEN 'SQL Server Agent is stopped'
WHEN 2 THEN 'SQL Server Agent is paused'
WHEN 6 THEN 'SQL Server Agent is in transition from paused to running'
WHEN 7 THEN 'SQL Server Agent is in transition from running to paused'
WHEN 4 THEN 'SQL Server Agent is in transition from stopped to running'
WHEN 5 THEN 'SQL Server Agent is in transition from running to stopped'
ELSE 'Unable to determine service execution state.'
END
SET @status = @property
-- clean up objects
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT
SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
RETURN
END
-- if no errors return status information
SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
GO
/* End of Stored Procedure */
With Rs
.ActiveConnection = gCon_cn
.Open "kt_sysAgentStatus '" & App.title & "','" & gStr_Password & "'"
If IsNull(Rs("Source").value) Then
If Not IsNull(Rs("Status").value) Then If Rs("Status").value = 1 Then mBol_AgentRunning = True
If Not IsNull(Rs("Status_msg").value) Then buffer = Rs("Status_msg").value
Else
If Not IsNull(Rs("Source").value) Then buffer = "AGENT STATUS ERROR : " & Rs("Status").value & " - "
If Not IsNull(Rs("Descripton").value) Then buffer = buffer & Rs("Description").value
End If
No comments have been posted about Get SQL Server Agent Status from client M\C without having SQL Server Client objects installed loca. Why not be the first to post a comment about Get SQL Server Agent Status from client M\C without having SQL Server Client objects installed loca.
